Create Or Replace View VW_FOL_PONTO_INTRANET As
Select
--DADOS DE APONTAMENTO
TO_CHAR(A.DATACC,'DD/MM/YYYY') DATACC,
A.SEMANA,
Min(A.CODHOR) CODHOR,
Min(ROWTOCOL('Select X.HORA
                From NBS.VW_FOL_REGISTRO_PONTO X
                Where X.NUMCAD = '||A.NUMCAD||'
                      And X.DAT_NUMBER = '||A.DAT_NUMBER||'
                      And X.CODSIT = '||A.CODSIT||'
                      And X.HORACC Is Not Null
                Order By X.HORACC', ' - ')) APONTAMENTO,
Max(DECODE(A.CODSIT,1,A.HORA_TOT,Null)) HORA_HT,
Max(DECODE(A.CODSIT,1,Null,Decode(A.SINSIT,'+',A.HORA_TOT,Null))) HORA_BC,
Max(DECODE(A.CODSIT,1,Null,Decode(A.SINSIT,'-',A.HORA_TOT,Null))) HORA_BD,
--DADOS DO CABECALHO
Min(B.NUMEMP) NUMEMP,
Min(B.RAZSOC) RAZSOC,
Min(B.NUMCGC) NUMCGC,
Min(B.ENDFIL) ENDFIL,
Min(B.ATVFIL) ATVFIL,
Min(B.NOMCID) NOMCID,
Min(B.CODEST) CODEST,
Min(B.NOMBAI) NOMBAI,
Min(B.NUMCAD) NUMCAD,
Min(B.NOMFUN) NOMFUN,
Min(B.CTPS) CTPS,
Min(B.NUMCPF) NUMCPF,
Min(B.NUMPIS) NUMPIS,
Min(B.CODCCU) CODCCU,
Min(B.NOMCCU) NOMCCU,
Min(B.CODCAR) CODCAR,
Min(B.TITRED) TITRED,
Min(B.CODESC) CODESC,
Min(B.NOMESC) NOMESC,
Min(B.MES_REF) MES_REF,
Min(B.SALDO_ANT) SALDO_ANT,
Min(B.HORA_CRED) HORA_CRED,
Min(B.HORA_DEB) HORA_DEB,
Min(B.HORA_PAG) HORA_PAG,
Min(B.SALDO_FINAL) SALDO_FINAL
From
VW_FOL_REGISTRO_PONTO A,
VW_FOL_CAB_REGISTRO_PONTO B
Where
A.NUMCPF = B.NUMCPF
Group By
A.DATACC,
A.SEMANA
Union
Select 
--AA.*,
TO_CHAR(AA.DATACC,'DD/MM/YYYY') DATACC,
AA.SEMANA,
C.CODHOR,
Decode(C.CODHOR,1,Null,D.DESHOR) APONTAMENTO,
Null HORA_HT,
Null HORA_BC,
Null HORA_BD,
--DADOS DO CABECALHO
B.NUMEMP,
B.RAZSOC,
B.NUMCGC,
B.ENDFIL,
B.ATVFIL,
B.NOMCID,
B.CODEST,
B.NOMBAI,
B.NUMCAD,
B.NOMFUN,
B.CTPS,
B.NUMCPF,
B.NUMPIS,
B.CODCCU,
B.NOMCCU,
B.CODCAR,
B.TITRED,
B.CODESC,
B.NOMESC,
B.MES_REF MES_REF,
B.SALDO_ANT SALDO_ANT,
B.HORA_CRED HORA_CRED,
B.HORA_DEB HORA_DEB,
B.HORA_PAG HORA_PAG,
B.SALDO_FINAL SALDO_FINAL
From (Select TO_DATE(TO_CHAR(Rownum,'00')||'/'||TO_CHAR(PKG_FOLHA.DAT_INICIO,'MM/YYYY'),'DD/MM/YYYY') DATACC,
             TO_CHAR(TO_DATE(TO_CHAR(Rownum,'00')||'/'||TO_CHAR(PKG_FOLHA.DAT_INICIO,'MM/YYYY'),'DD/MM/YYYY'),'DY','NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA
        From DUAL Connect By Level <= PKG_FOLHA.DAT_FIM - PKG_FOLHA.DAT_INICIO + 1) AA,
VW_FOL_CAB_REGISTRO_PONTO B,
FOLHA.R006HOR C,
FOLHA.R004HOR D
Where
B.NUMCPF = PKG_FOLHA.NUM_CPF And
C.CODHOR = D.CODHOR And
C.CODESC = B.CODESC And
C.SEQREG = TO_NUMBER(TO_CHAR(AA.DATACC,'D')) And
AA.DATACC Not In (Select XX.DATACC
                    From VW_FOL_REGISTRO_PONTO XX
                    Group By XX.DATACC
                  Union
                  Select XX.DATACC + (Rownum - 1)
                    From (Select E.DATAFA DATACC,
                                 E.DATTER - E.DATAFA + 1 QTD_DIAS
                            From FOLHA.R038AFA E,
                                 FOLHA.R010SIT F
                            Where E.NUMCAD = PKG_FOLHA.NUM_CAD And
                                  E.SITAFA = F.CODSIT And
                                  E.DATAFA >= PKG_FOLHA.DAT_INICIO And
                                  E.DATAFA <= PKG_FOLHA.DAT_FIM) XX
                    Connect By Level <= XX.QTD_DIAS
                  Union
                  Select AA.DATFER
                    From FOLHA.R002FEC AA,
                         FOLHA.R002FEM BB,
                         VW_FOL_CAB_REGISTRO_PONTO CC
                    Where AA.CODFER = BB.CODFER And 
                          AA.CODFER = CC.TABFEN And 
                          AA.DATFER >= PKG_FOLHA.DAT_INICIO And 
                          AA.DATFER <= PKG_FOLHA.DAT_FIM And 
                          CC.NUMCAD = PKG_FOLHA.NUM_CAD
                    Group By AA.DATFER
                  Union
                  Select A.DATAPU
                    From FOLHA.R066SIT A
                    Where A.CODSIT In (308) And
                          A.NUMCAD = PKG_FOLHA.NUM_CAD And
                          A.DATAPU Not In (Select X.DATACC
                                             From VW_FOL_REGISTRO_PONTO X
                                             Group By X.DATACC)
                    Group By A.DATAPU)
And AA.DATACC <= Trunc(Sysdate)
Union
Select
TO_CHAR(AA.DATACC,'DD/MM/YYYY') DATACC,
AA.SEMANA,
C.CODHOR,
AA.DESSIT APONTAMENTO,
Null HORA_HT,
Null HORA_BC,
Null HORA_BD,
--DADOS DO CABECALHO
B.NUMEMP,
B.RAZSOC,
B.NUMCGC,
B.ENDFIL,
B.ATVFIL,
B.NOMCID,
B.CODEST,
B.NOMBAI,
B.NUMCAD,
B.NOMFUN,
B.CTPS,
B.NUMCPF,
B.NUMPIS,
B.CODCCU,
B.NOMCCU,
B.CODCAR,
B.TITRED,
B.CODESC,
B.NOMESC,
B.MES_REF MES_REF,
B.SALDO_ANT SALDO_ANT,
B.HORA_CRED HORA_CRED,
B.HORA_DEB HORA_DEB,
B.HORA_PAG HORA_PAG,
B.SALDO_FINAL SALDO_FINAL
From (Select XX.DATACC + (Rownum - 1) DATACC,
             TO_CHAR(XX.DATACC + (Rownum - 1),'DY','NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA,
             XX.DESSIT
        From (Select E.DATAFA DATACC,
                     F.DESSIT,
                     E.DATTER - E.DATAFA + 1 QTD_DIAS
                From FOLHA.R038AFA E,
                     FOLHA.R010SIT F
                Where E.NUMCAD = PKG_FOLHA.NUM_CAD And
                      E.SITAFA = F.CODSIT And
                      E.DATAFA >= PKG_FOLHA.DAT_INICIO And
                      E.DATAFA <= PKG_FOLHA.DAT_FIM) XX
        Connect By Level <= XX.QTD_DIAS) AA,
VW_FOL_CAB_REGISTRO_PONTO B,
FOLHA.R006HOR C,
FOLHA.R004HOR D
Where
B.NUMCPF = PKG_FOLHA.NUM_CPF And
C.CODHOR = D.CODHOR And
C.CODESC = B.CODESC And
C.SEQREG = TO_NUMBER(TO_CHAR(AA.DATACC,'D'))
And AA.DATACC <= Trunc(Sysdate)
Union
--DADOS DO FERIADO
Select 
TO_CHAR(AA.DATACC,'DD/MM/YYYY') DATACC,
AA.SEMANA,
D.CODHOR,
D.DESHOR||' - '||AA.DESFER APONTAMENTO,
Null HORA_HT,
Null HORA_BC,
Null HORA_BD,
--DADOS DO CABECALHO
B.NUMEMP,
B.RAZSOC,
B.NUMCGC,
B.ENDFIL,
B.ATVFIL,
B.NOMCID,
B.CODEST,
B.NOMBAI,
B.NUMCAD,
B.NOMFUN,
B.CTPS,
B.NUMCPF,
B.NUMPIS,
B.CODCCU,
B.NOMCCU,
B.CODCAR,
B.TITRED,
B.CODESC,
B.NOMESC,
B.MES_REF MES_REF,
B.SALDO_ANT SALDO_ANT,
B.HORA_CRED HORA_CRED,
B.HORA_DEB HORA_DEB,
B.HORA_PAG HORA_PAG,
B.SALDO_FINAL SALDO_FINAL
From (Select AA.DATFER DATACC,
             TO_CHAR(AA.DATFER,'DY','NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA,
             Min(AA.DESFER) DESFER
        From FOLHA.R002FEC AA,
             FOLHA.R002FEM BB,
             VW_FOL_CAB_REGISTRO_PONTO CC
        Where AA.CODFER = BB.CODFER And 
              AA.CODFER = CC.TABFEN And 
              AA.DATFER >= PKG_FOLHA.DAT_INICIO And 
              AA.DATFER <= PKG_FOLHA.DAT_FIM And 
              CC.NUMCAD = PKG_FOLHA.NUM_CAD And
              AA.DATFER Not In (Select X.DATACC
                                  From VW_FOL_REGISTRO_PONTO X
                                  Group By X.DATACC
                                Union
                                Select XX.DATACC + (Rownum - 1)
                                  From (Select E.DATAFA DATACC,
                                               E.DATTER - E.DATAFA + 1 QTD_DIAS
                                          From FOLHA.R038AFA E,
                                               FOLHA.R010SIT F
                                          Where E.NUMCAD = PKG_FOLHA.NUM_CAD And
                                                E.SITAFA = F.CODSIT And
                                                E.DATAFA >= PKG_FOLHA.DAT_INICIO And
                                                E.DATAFA <= PKG_FOLHA.DAT_FIM) XX
                                  Connect By Level <= XX.QTD_DIAS)
        Group By AA.DATFER) AA,
VW_FOL_CAB_REGISTRO_PONTO B,
FOLHA.R004HOR D
Where
B.NUMCPF = PKG_FOLHA.NUM_CPF And
D.CODHOR In (9997)
And AA.DATACC <= Trunc(Sysdate)
Union
--DADOS DE BANCO DE HORAS
Select 
TO_CHAR(A.DATAPU,'DD/MM/YYYY') DATACC,
TO_CHAR(A.DATAPU,'DY','NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA,
A.CODSIT CODHOR,
C.DESSIT APONTAMENTO,
Null HORA_HT,
Null HORA_BC,
TRIM(TO_CHAR(TRUNC(A.QTDHOR / 60),'00'))||':'||TRIM(TO_CHAR(MOD(A.QTDHOR,60),'00')) HORA_BD,
--DADOS DO CABECALHO
B.NUMEMP,
B.RAZSOC,
B.NUMCGC,
B.ENDFIL,
B.ATVFIL,
B.NOMCID,
B.CODEST,
B.NOMBAI,
B.NUMCAD,
B.NOMFUN,
B.CTPS,
B.NUMCPF,
B.NUMPIS,
B.CODCCU,
B.NOMCCU,
B.CODCAR,
B.TITRED,
B.CODESC,
B.NOMESC,
B.MES_REF MES_REF,
B.SALDO_ANT SALDO_ANT,
B.HORA_CRED HORA_CRED,
B.HORA_DEB HORA_DEB,
B.HORA_PAG HORA_PAG,
B.SALDO_FINAL SALDO_FINAL
From
FOLHA.R066SIT A,
VW_FOL_CAB_REGISTRO_PONTO B,
FOLHA.R010SIT C
Where
A.NUMCAD = B.NUMCAD And
A.CODSIT = C.CODSIT And
A.CODSIT In (308) And
A.NUMCAD = PKG_FOLHA.NUM_CAD
AND A.DATAPU >= PKG_FOLHA.DAT_INICIO
AND A.DATAPU <= PKG_FOLHA.DAT_FIM
And A.DATAPU Not In (Select X.DATACC
                       From VW_FOL_REGISTRO_PONTO X
                       Group By X.DATACC)
Order By 1
